﻿using System;
using System.IO;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Engine;
using System.Collections.Generic;
using NHibernate.Criterion.Restrictions;
namespace Malahit_1
{
    public class City
    {
        private int city_id;
        private string name;

        public virtual int City_id
        {
            get { return city_id; }
            protected set { city_id = value; }
        }
        public virtual string Name
        {
            get { return name; }
            protected set { name = value; }
        }
        public City()
        {
        }
        public City(int city_id, string name)
        {
            this.city_id = city_id;
            this.name = name;
        }
    }
    public class Firm
    {
        private int firm_id;
        private string name;
        private int jur_city_id;
        private int post_city_id;

        public virtual int Firm_id
        {
            get { return firm_id; }
            protected set { firm_id = value; }
        }
        public virtual string Name
        {
            get { return name; }
            protected set { name = value; }
        }
        public virtual int Jur_city_id
        {
            get { return jur_city_id; }
            protected set { jur_city_id = value; }
        }
        public virtual int Post_city_id
        {
            get { return post_city_id; }
            protected set { post_city_id = value; }
        }
    }
    public class DataGetter
    {
        private ISessionFactory sessionFactory;

        public DataGetter()
        {
            ISessionFactory sessionFactory = new Configuration().Configure("Nhibernate.cfg.xml").BuildSessionFactory();
        }
        public IList<City> GetAllCities()
        {
            ISession session = sessionFactory.OpenSession();
            IList<City>  result= session.CreateCriteria(typeof(City)).List<City>();
            session.Close();
            return result;
        }
        public IList<Firm> GetFirms()
        {
            ISession session = sessionFactory.OpenSession();
            ICriteria criteria = session.CreateCriteria(typeof(Firm));
            criteria.Add(Restrictions.Like("Name",
            IList<Firm> result = session.
            session.Close();
        }
        ~DataGetter()
        {
            sessionFactory.Close();
        }
    }
    public class MainForm : Form
    {
        private const string CONFIG_FILE = "config.txt";
        private SqlConnection conn; // соединение с БД
        private Panel buttonPanel = new Panel();
        private Panel filterPanel = new Panel();
        private RadioButton radioButton1 = new RadioButton();
        private RadioButton radioButton2 = new RadioButton();
        private DataGridView cityDataGridView = new DataGridView();
        private DataGridView firmDataGridView = new DataGridView();
        private DataSet data;
        private SqlDataAdapter cityDataAdapter;
        private SqlDataAdapter firmDataAdapter;
        private BindingSource cityBindingSource = new BindingSource();
        private BindingSource firmBindingSource = new BindingSource();

        private Label firmnameLabel = new Label();
        private Label citynameLabel = new Label();
        private TextBox firmnameTextBox = new TextBox();
        private TextBox citynameTextBox = new TextBox();
        private Button searchButton = new Button();

        public MainForm()
        {
            // Устанавливаем соединение

            int city_id=11;
           
            City city=session.Get<City>(city_id);
           
             if (!File.Exists(CONFIG_FILE))
            {
                MessageBox.Show("Нет файла с конфигурацией подключения к БД","Ошибка",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
             try
             {
                 this.conn = new System.Data.SqlClient.SqlConnection(File.ReadAllText(CONFIG_FILE));
             }
             catch (SqlException ex)
             {
                 MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
            this.Load += new EventHandler(MainForm_Load);
        }

        private void MainForm_Load(System.Object sender, System.EventArgs e)
        {
            SetupLayout();
            SetupDataGridView();
        }

        private void SetupLayout()
        {
            this.Size = new Size(600, 500);
            this.Text = "Тестовое задание 1";
 
            firmnameLabel.Location = new Point(10, 10);
            firmnameLabel.Text = "Название фирмы";
            citynameLabel.Location = new Point(10, 40);
            citynameLabel.Text = "Город";

            firmnameTextBox.Location = new Point(110, 10);
            citynameTextBox.Location = new Point(110, 40);

            radioButton1.Location = new Point(300, 10);
            radioButton1.Text = "По юр.адресу";
            radioButton1.Checked = true;
            radioButton1.Click+=new EventHandler(radioButton_Click);

            radioButton2.Location = new Point(300, 40);
            radioButton2.Text = "По почтовому адресу";
            radioButton2.Click += new EventHandler(radioButton_Click);

            filterPanel.Controls.Add(firmnameLabel);
            filterPanel.Controls.Add(citynameLabel);
            filterPanel.Controls.Add(firmnameTextBox);
            filterPanel.Controls.Add(citynameTextBox);
            filterPanel.Controls.Add(radioButton1);
            filterPanel.Controls.Add(radioButton2);

            filterPanel.Height = 80;
            filterPanel.Dock = DockStyle.Top;
           

            searchButton.Text = "Поиск";
            searchButton.Location = new Point(0, 10);
            searchButton.Click += new EventHandler(searchButton_Click);

            buttonPanel.Controls.Add(searchButton);
            buttonPanel.Height = 50;
            buttonPanel.Dock = DockStyle.Top;

            cityDataGridView.Dock = DockStyle.Fill;
            firmDataGridView.Dock = DockStyle.Fill;
            SplitContainer splitcontainer = new SplitContainer();
            splitcontainer.Dock = DockStyle.Top;
            splitcontainer.Height = 350;
            splitcontainer.Panel1.Controls.Add(cityDataGridView);
            splitcontainer.Panel2.Controls.Add(firmDataGridView);
            this.Controls.Add(this.buttonPanel);
            this.Controls.Add(filterPanel);
            this.Controls.Add(splitcontainer);

        }
        private void SetupDataGridView()
        {
            try
            {
                cityDataGridView.DataSource = cityBindingSource;
                firmDataGridView.DataSource = firmBindingSource;
                //GetData();
                
                cityDataGridView.AutoResizeColumns();
                cityDataGridView.ReadOnly = true;
                firmDataGridView.ReadOnly = true;
                firmDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Ошибка");
                System.Threading.Thread.CurrentThread.Abort();
            }

        }
        private void GetData()
        {
            try
            {
                data = new DataSet();
                data.Locale = System.Globalization.CultureInfo.InvariantCulture;
                
                cityDataAdapter = new SqlDataAdapter("select * from city", this.conn);
                cityDataAdapter.Fill(data, "City");
                data.Tables["City"].Columns["city_id"].ReadOnly = true;
    
   
                /*Создаем адаптер для связанной таблицы */
                firmDataAdapter = new SqlDataAdapter("select * from firm", this.conn);
                firmDataAdapter.Fill(data, "Firm");
              
                /*Устанавливаем отношение*/
                DataRelation relation = new DataRelation("CityFirm",
                data.Tables["City"].Columns["city_id"],
                data.Tables["Firm"].Columns["jur_city_id"]);

                DataRelation relation1 = new DataRelation("PostCityFirm",
                data.Tables["City"].Columns["city_id"],
                data.Tables["Firm"].Columns["post_city_id"]);
                data.Relations.Add(relation);
                data.Relations.Add(relation1);
                cityBindingSource.DataSource = data;
                cityBindingSource.DataMember = "City";

                firmBindingSource.DataSource = cityBindingSource;
                firmBindingSource.DataMember = "CityFirm";
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void radioButton_Click(object sender, EventArgs e)
        {
            if (radioButton1.Checked)
            {
                firmBindingSource.DataMember = "CityFirm";
            }
            else
            {
                firmBindingSource.DataMember = "PostCityFirm";
            }
        }
        private void searchButton_Click(object sender, EventArgs e)
        {
            try
            {
                if (citynameTextBox.TextLength > 0)
                {
                    cityDataAdapter.SelectCommand.CommandText = "SELECT * FROM city WHERE name=@cityname";
                    cityDataAdapter.SelectCommand.Parameters.Clear();
                    cityDataAdapter.SelectCommand.Parameters.Add("@cityname", SqlDbType.VarChar, 20).Value = citynameTextBox.Text;

                    firmDataAdapter.SelectCommand.CommandText = @"SELECT Firm.* from firm INNER JOIN City ON City.city_id=firm.jur_city_id 
                                                                 WHERE city.name=@cityname";
                    firmDataAdapter.SelectCommand.Parameters.Clear();
                    firmDataAdapter.SelectCommand.Parameters.Add("@cityname", SqlDbType.VarChar, 20).Value = citynameTextBox.Text;
                    if (firmnameTextBox.TextLength > 0)
                    {
                        firmDataAdapter.SelectCommand.CommandText += " AND firm.name=@firmname";
                        firmDataAdapter.SelectCommand.Parameters.Add("@firmname", SqlDbType.VarChar, 255).Value = firmnameTextBox.Text;
                    }
                }
                else
                {
                    cityDataAdapter = new SqlDataAdapter("select * from city", this.conn);
                    if (firmnameTextBox.TextLength > 0)
                    {
                        firmDataAdapter.SelectCommand.CommandText = "SELECT * from firm WHERE firm.name=@firmname";
                        firmDataAdapter.SelectCommand.Parameters.Clear();
                        firmDataAdapter.SelectCommand.Parameters.Add("@firmname", SqlDbType.VarChar, 255).Value = firmnameTextBox.Text;
                    }
                    else
                    {
                        firmDataAdapter = new SqlDataAdapter("select * from firm", this.conn);
                    }
                }
                data.Clear();
                cityDataAdapter.Fill(data, "City");
                firmDataAdapter.Fill(data, "Firm");
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}